// 用户相关数据库查询
import { getDB } from './index.js'

/**
 * 清理 userId，去除前后引号和空格
 * @param {string} userId 用户ID
 * @returns {string} 清理后的用户ID
 */
function cleanUserId(userId) {
  if (!userId || typeof userId !== 'string') {
    return userId
  }
  // 去除前后引号（单引号和双引号）和空格
  return userId.trim().replace(/^["']+|["']+$/g, '')
}

/**
 * 创建或更新用户
 * @param {object} userData 用户数据
 * @param {string} userData.userId 用户ID
 * @param {string} userData.type 用户类型（weixin/guest）
 * @param {string} [userData.openid] 微信openid
 * @param {string} [userData.unionid] 微信unionid
 * @param {string} [userData.sessionKey] 微信session_key
 * @param {string} [userData.nickname] 昵称
 * @param {string} [userData.avatar] 头像
 * @param {string} [userData.platform] 平台
 * @param {string} [userData.lastLoginIp] 最后登录IP
 * @param {string} [userData.lastLoginUserAgent] 最后登录User-Agent
 * @returns {object} 用户对象
 */
export function upsertUser(userData) {
  const db = getDB()
  
  // 清理 userId，去除前后引号和空格
  const cleanId = cleanUserId(userData.userId)
  
  // 检查用户是否存在（使用清理后的 ID）
  const existingUser = getUserByUserId(cleanId)
  
  if (existingUser) {
    // 更新用户
    const stmt = db.prepare(`
      UPDATE User SET
        type = ?,
        openid = ?,
        unionid = ?,
        sessionKey = ?,
        nickname = ?,
        avatar = ?,
        platform = ?,
        lastLoginAt = datetime('now'),
        lastLoginIp = ?,
        lastLoginUserAgent = ?,
        updatedAt = datetime('now')
      WHERE userId = ?
    `)
    
    stmt.run(
      userData.type,
      userData.openid ? cleanUserId(userData.openid) : null,
      userData.unionid ? cleanUserId(userData.unionid) : null,
      userData.sessionKey || null,
      userData.nickname || null,
      userData.avatar || null,
      userData.platform || null,
      userData.lastLoginIp || null,
      userData.lastLoginUserAgent || null,
      cleanId
    )
    
    return getUserByUserId(cleanId)
  } else {
    // 创建新用户
    const stmt = db.prepare(`
      INSERT INTO User (userId, type, openid, unionid, sessionKey, nickname, avatar, platform, lastLoginAt, lastLoginIp, lastLoginUserAgent)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, datetime('now'), ?, ?)
    `)
    
    stmt.run(
      cleanId,
      userData.type,
      userData.openid ? cleanUserId(userData.openid) : null,
      userData.unionid ? cleanUserId(userData.unionid) : null,
      userData.sessionKey || null,
      userData.nickname || null,
      userData.avatar || null,
      userData.platform || null,
      userData.lastLoginIp || null,
      userData.lastLoginUserAgent || null
    )
    
    return getUserByUserId(cleanId)
  }
}

/**
 * 根据userId获取用户
 * @param {string} userId 用户ID
 * @returns {object|null}
 */
export function getUserByUserId(userId) {
  const db = getDB()
  // 清理 userId 后再查询
  const cleanId = userId ? userId.trim().replace(/^["']+|["']+$/g, '') : userId
  const stmt = db.prepare('SELECT * FROM User WHERE userId = ?')
  return stmt.get(cleanId) || null
}

/**
 * 根据openid获取用户
 * @param {string} openid 微信openid
 * @returns {object|null}
 */
export function getUserByOpenid(openid) {
  const db = getDB()
  const stmt = db.prepare('SELECT * FROM User WHERE openid = ?')
  return stmt.get(openid) || null
}

/**
 * 获取用户列表（管理员用）
 * @param {object} options 查询选项
 * @param {string} [options.type] 用户类型
 * @param {string} [options.platform] 平台
 * @param {number} [options.status] 状态
 * @param {number} [options.page] 页码
 * @param {number} [options.pageSize] 每页数量
 * @returns {object} { list, total, page, pageSize }
 */
export function getUsers(options = {}) {
  const db = getDB()
  const {
    type,
    platform,
    status,
    page = 1,
    pageSize = 20
  } = options

  let whereClause = 'WHERE 1=1'
  const params = []

  if (type) {
    whereClause += ' AND type = ?'
    params.push(type)
  }

  if (platform) {
    whereClause += ' AND platform = ?'
    params.push(platform)
  }

  if (status !== undefined) {
    whereClause += ' AND status = ?'
    params.push(status)
  }

  // 查询总数
  const countStmt = db.prepare(`SELECT COUNT(*) as total FROM User ${whereClause}`)
  const totalResult = countStmt.get(...params)
  const total = totalResult.total

  // 查询列表
  const offset = (page - 1) * pageSize
  const listStmt = db.prepare(`
    SELECT 
      id,
      userId,
      type,
      openid,
      unionid,
      nickname,
      avatar,
      platform,
      status,
      lastLoginAt,
      lastLoginIp,
      lastLoginUserAgent,
      createdAt,
      updatedAt
    FROM User
    ${whereClause}
    ORDER BY createdAt DESC
    LIMIT ? OFFSET ?
  `)

  const list = listStmt.all(...params, pageSize, offset)

  return {
    list,
    total,
    page,
    pageSize
  }
}

/**
 * 更新用户状态
 * @param {string} userId 用户ID
 * @param {number} status 状态
 * @returns {boolean}
 */
export function updateUserStatus(userId, status) {
  const db = getDB()
  const stmt = db.prepare('UPDATE User SET status = ?, updatedAt = datetime(\'now\') WHERE userId = ?')
  const result = stmt.run(status, userId)
  return result.changes > 0
}

/**
 * 删除用户
 * @param {string} userId 用户ID
 * @returns {boolean}
 */
export function deleteUser(userId) {
  const db = getDB()
  const stmt = db.prepare('DELETE FROM User WHERE userId = ?')
  const result = stmt.run(userId)
  return result.changes > 0
}

/**
 * 获取用户统计
 * @returns {object}
 */
export function getUserStats() {
  const db = getDB()
  
  // 总用户数
  const totalStmt = db.prepare('SELECT COUNT(*) as total FROM User')
  const total = totalStmt.get().total
  
  // 微信用户数
  const weixinStmt = db.prepare("SELECT COUNT(*) as count FROM User WHERE type = 'weixin'")
  const weixinCount = weixinStmt.get().count
  
  // 游客用户数
  const guestStmt = db.prepare("SELECT COUNT(*) as count FROM User WHERE type = 'guest'")
  const guestCount = guestStmt.get().count
  
  // 今日新增用户数
  const todayStmt = db.prepare(`
    SELECT COUNT(*) as count
    FROM User
    WHERE date(createdAt) = date('now')
  `)
  const todayCount = todayStmt.get().count
  
  return {
    total,
    weixinCount,
    guestCount,
    todayCount
  }
}

